from typing import List

from fastapi import APIRouter, Request, HTTPException
from pydantic import BaseModel, field_validator, ValidationError
from starlette.templating import Jinja2Templates

from apps.app09_orm import Student, Clas, Course

app10 = APIRouter()

templates = Jinja2Templates(directory="templates")

class StudentIn(BaseModel):
    name:str
    username :str
    password :str
    stuNo:str
    clas_id:int
    course:List[int] = []


    @classmethod
    @field_validator('name')
    def name_must_alpha(cls,value:str):
        """验证名字智能还包含字母和中文"""
        if not value.isalpha():
            raise ValidationError('姓名智能包含字母和中文')
        return value

    @classmethod
    @field_validator('stuNo')
    def stuNo_must_positive(cls,value:int):
        """验证学号必须是正数"""
        if value <= 0:
            raise ValidationError('学号必须是正数')
        return value

@app10.delete('/{student_id}')
async def delete_student(student_id:int):
    rows = await Student.filter(id=student_id).delete()
    if not rows:
        raise HTTPException(status_code=404, detail="您要删除的学生不存在")
    else:
        return {
            'success':f'{rows}条数据删除成功'
        }

@app10.put('/{student_id}')
async def update_student(student_id:int,student_in:StudentIn):
    # 需要将课程取出来 修改完学生信息之后，在修改 学生和课程中间表
    # 可以先将 学生兑现转换成 字典形式取出
    # data = student_in.dict() # 过时方法
    data = student_in.model_dump() # 将对象转换成字典
    # 将课程取出 因为关联字段（如 ForeignKey、ManyToManyField）通常是虚拟字段，不能直接通过 update() 方法更新。
    courses = data.pop('course')

    # 更新学生信息
    await Student.filter(id=student_id).update(**data)

    # 设置多对多的选课信息
    edit_student = await Student.get(id=student_id)
    choose_course = await Course.get(id__in=courses)
    await edit_student.course.clear() # 将该学生的选课信息清除
    await edit_student.course.add(choose_course) # 在将选课信息重新添加


@app10.post('/student')
async def add_student(student_in:StudentIn):
    # 创建用户方式一
    # student = Student(
    #     name=student_in.name,
    #     username=student_in.username,
    #     password=student_in.password,
    #     stuNo=student_in.stuNo,
    #     clas_id=student_in.clas_id,
    # )
    # # 添加到数据库
    # await student.save()

    # 创建用户方式二
    student = await Student.create(
        name=student_in.name,
        username=student_in.username,
        password=student_in.password,
        stuNo=student_in.stuNo,
        clas_id=student_in.clas_id,
    )

    # 绑定多对多的关系
    choos_courses = await Course.filter(id__in=student_in.course)
    print(choos_courses)
    await student.course.add(*choos_courses)

    return student


@app10.get('/find')
async def select_test(request: Request):
    # 1. 查询所有 all方法
    # students = await Student.all()  #Queryset: [Student(),Student(),Student()]
    # print(students)
    # print(type(students))
    # for student in students:
    #     print(student.name,student.username)

    # 2. 过滤查询 filter  查询clas_id等于1的学生信息
    # students = await Student.filter(clas_id = 1)
    # for student in students:
    #     print(student.name)


    # 3. 过滤查找 get 查询id是1的学生信息
    # student = await Student.get(id=1) # Student()
    # student = await Student.filter(id=1).first()
    # print(student.name)

    # 4. 根据范围查找 filter
    # students = await Student.filter(stuNo__gt=1002)  # 小于是lt 查询学号大于1002的学生信息

    # 查询学号在1001-1003之间的学生信息
    # students = await Student.filter(stuNo__range=[1001,1003])

    # 查询学号是1001或者1003的学生信息
    # students = await Student.filter(stuNo__in=[1001, 1003])
    #
    # for student in students:
    #     print(student.id)

    # 5. values查询
    # select stuNo from student
    # students = await Student.all().values('stuNo','name') # 返回字典 {'stuNo': '1001', 'name': '李欣桐'}
    #
    # for student in students:
    #     # print(student.get('stuNo'))
    #     print(student)

    # 6. 模糊查询
    # 6.1 查询所有姓名中包含李字的
    # students = await Student.filter(name__contains='李')
    # 6.2 查询以李字开头的
    # students = await Student.filter(name__startswith="李")
    # 6.3 查询姓名以桐字结尾的
    # students = await  Student.filter(name__endswith="桐")
    # for student in students:
    #     print(student.name)

    # 7. 排序查找
    # 7.1 按照学号升序排序
    # students = await Student.all().order_by("stuNo")
    # 7.2 按照学号降序排序
    # students = await Student.all().order_by("-stuNo")
    # # 7.2 多字段排序，按照班级升序，按照学号降序
    # students = await Student.all().order_by('clas_id',"-stuNo")

    # 7.3 结合过滤和排序，查找班级id是2，按照学号降序
    # students = await Student.filter(clas__id=2).order_by("-stuNo")
    # for student in students:
    #     print(student.name, student.stuNo,student.clas_id)
    #
    # clas = await Clas.get(id=student.clas_id)
    # return templates.TemplateResponse("studentList.html", {
    #     "request": request,
    #     'stus': students,
    #     'clasName': clas.name
    # })


    # 一对多查询 ，查询学生的班级
    # student = await Student.get(name = '李欣桐')
    # print(student)
    # print(student.stuNo)
    # print(await student.clas.values('name'))

    # 查找所有学生的班级
    # students = await Student.all().values('name','username','clas__name')
    # for student in students:
    #     print(student.get('name'))
    #     print(student.get('username'))
    #     print(student.get('clas__name'))

    # return templates.TemplateResponse('studentList.html', {
    #     'request': request,
    #     'stus':students,
    # })

    # 多对多
    # 查询 姚晓冲 报了哪些课程，老师是谁
    # student = await Student.get(name = '姚晓冲')
    # print(await student.course.all().values('name','teacher__name'))

    # 查询所有学生报了哪些课，
    students = await Student.all().values('name','clas__name','course__name')
    print(students)


